package com.rykj.qxj.server.util;

import org.apache.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2017/8/11/011.
 */
public class JdbcClient {

    private Logger logger = Logger.getLogger(JdbcClient.class);
    private Connection connection = null;
    public Statement stmt = null;
    public String user = "";

    public JdbcClient(String url, String user, String password, String driver) throws SQLException, ClassNotFoundException {
        this.user = user;
        Class.forName(driver);
        connection = DriverManager.getConnection(url, user, password);
    }

    /**
     * sql语句执行
     * @param sql
     * @throws Exception
     */
    public boolean excuteSql(String sql) throws Exception {

        boolean b = false;
        try {
            if (stmt == null || stmt.isClosed()) {
                stmt = connection.createStatement();
            }
            b = stmt.execute(sql);
        }catch (Exception e){
            logger.error("sql执行异常",e);
            throw new Exception(e.getMessage());
        }finally {
            closeJdbcConnection(stmt);
        }
        return b;
    }

    /**
     * @Description 查询
     * @param sql
     *            sql语句
     * @return 结果集列表
     */
    public List<Map<String, Object>> query(String sql) {
        List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
        try {
            if (stmt == null || stmt.isClosed()) {
                stmt = connection.createStatement();
            }
            ResultSet res = stmt.executeQuery(sql);
            ResultSetMetaData meta = res.getMetaData();
            while (res.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= meta.getColumnCount(); i++)
                    map.put(meta.getColumnLabel(i), res.getObject(i));
                listmap.add(map);
            }
            res.close();
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        } finally {
            closeJdbcConnection(stmt);
        }
        return listmap;
    }

    /**
     * @Description 查询
     * @param sql
     *            查询语句
     * @param parameters
     *            参数列表
     * @return 结果集列表
     */
    public List<Map<String, Object>> query(String sql,List<Object> parameters) throws Exception {
        List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
        PreparedStatement stmt = null;
        try {
            ResultSet res = null;
            stmt = connection.prepareStatement(sql);

            if (null != parameters) {
                int i = 1;
                for (Object parameter : parameters) {
                    stmt.setObject(i, parameter);
                    i++;
                }
            }
            res = stmt.executeQuery();

            ResultSetMetaData meta = res.getMetaData();
            while (res.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= meta.getColumnCount(); i++)
                    map.put(meta.getColumnLabel(i), res.getObject(i));
                listmap.add(map);
            }
            res.close();
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        } finally {
            closeJdbcConnection(stmt);
        }
        return listmap;
    }


    /**
     * @Description 执行sql
     * @param sql
     *            语句
     * @param parameters
     *            参数列表
     * @return
     */
    public boolean execSql(String sql,List<Object> parameters) {
        PreparedStatement stmt = null;
        try {
            stmt = connection.prepareStatement(sql);
            if (null != parameters) {
                int i = 1;
                for (Object parameter : parameters) {
                    stmt.setObject(i, parameter);
                    i++;
                }
            }
            stmt.executeUpdate();
        } catch (Exception e) {
            logger.error("fail sql:" + sql,e);
            return false;
        } finally {
            closeJdbcConnection(stmt);
        }
        return true;
    }

    /**
     * 获取表字段
     * @param tableName
     * @return
     * @throws Exception
     */
    public List<String> getTableField(String tableName){
        List<String> list = new ArrayList<>();
        try {
            DatabaseMetaData metadata = connection.getMetaData();
            String columnName;
            ResultSet colRet = metadata.getColumns(null,user.toUpperCase(), tableName, null);
            while(colRet.next()) {
                columnName = colRet.getString("COLUMN_NAME");
                list.add(columnName);
            }
        }catch (Exception e){
             e.printStackTrace();
        }finally{
           closeJdbcConnection(stmt);
        }
        return list;
    }
    public void closeJdbcConnection(Statement stmt){
        try {
            if(stmt!=null){
                stmt.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            logger.error("session关闭异常",e);
        }
    }

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        final String oracle_url = "jdbc:oracle:thin:@10.153.98.22:1521:SZQX1";
        final String user_name = "szjk";
        final String pass_word = "pwdjk2019";
        final String oracle_driver = "oracle.jdbc.driver.OracleDriver";
        JdbcClient jdbcClient = new JdbcClient(oracle_url,user_name,pass_word,oracle_driver);
        System.out.println(jdbcClient);
        jdbcClient.closeJdbcConnection(jdbcClient.stmt);
    }
}
